PostgreSQL Server kraken setup
This page is for recording significant configuration settings for the dedicated PostgreSQL server kraken. It exists in concert with the wiki page for the pgtest server where various system and PostgreSQL parameters are tuned for optimal performance on a ESX virtual machine. The parameters for kraken will be deferent than for pgtest; however, pgtest provides a useful test environment for proposed changes.
Hardware Documentation
ioMemory_VSL_3.2.3_User_Guide_for_Linux.pdf (application/pdf)
ioMemory_VSL_3.2.3_Release_Notes.pdf (application/pdf)
kraken_sar_1aug2013.txt (text/plain)
sudo yum install gnuplot 
Posted by mccann at Jul 10, 2013 16:55
|
|
Configuring kraken to run pgbench-tools as user mccann on kraken: cd /var/www/html sudo mkdir pgbench-tools sudo chown postgres pgbench-tools Copy pgbench-tools from its special configuration on pgtest for passwordless connection to the database, as postgres@pgtest: cd /var/lib/pgsql/dev/pgbench-tools tar cvf /tmp/pgbench-tools.tar * Moved the .tar file from pgtest to kraken, then install in the web directory rather than in postgres's home directory. As postgres on kraken: cd /var/www/html/pgbench-tools cp /tmp/pgbench-tools.tar . tar xvf pgbench-tools.tar Setup pgbench-tools databases and run an initial test as postgres@kraken: createdb results createdb pgbench cd /var/www/html/pgbench-tools psql -f init/resultdb.sql -d results sudo yum install postgresql91-contrib # as mccann (needed for the pgbench binary) ./runset Configure web server on kraken to serve up results from pgbench-tools. First, submit help ticket to open up port 80 for http on kraken. sudo chkconfig httpd on sudo /sbin/service httpd start
Posted by mccann at Jul 11, 2013 11:17
|
|
Modifying system parameters that have been tested on pgtest. Here are the changes: <197 kraken.shore.mbari.org //etc> diff sysctl.conf.130712 sysctl.conf 40a41,53 > > # Swappiness set to 0 per p.81 in PostgreSQL 9.0 High Performance book. mpm - 12 July 2013 > vm.swappiness = 0 > # Reduce tendency to allocate more ram than the system has. mpm - 12 July 2013 > vm.overcommit_memory = 2 > > # Setting for shared memory = 1/2 physical RAM = 64 GB. mpm 12 July 2013 > ##kernel.shmmax = 67649847296 > ##kernel.shmall = 16516076 > > # Set shared memory to 4 GB - a better value for PostgreSQL on large memory systems. mpm 12 July 2013 > kernel.shmmax = 4228112384 > kernel.shmall = 1032254 <198 kraken.shore.mbari.org //etc> diff fstab.130712 fstab 11,13c11,13 < /dev/mapper/vg_kraken-LogVol02 /pg_xlog_spinning ext4 defaults 1 2 < /dev/mapper/vg_kraken-LogVol04 /pgdata_spinning ext4 defaults 1 2 < /dev/mapper/vg_kraken-LogVol03 /tablespace_spinning ext4 defaults 1 2 --- > /dev/mapper/vg_kraken-LogVol02 /pg_xlog_spinning ext4 defaults,noatime 1 2 > /dev/mapper/vg_kraken-LogVol04 /pgdata_spinning ext4 defaults,noatime 1 2 > /dev/mapper/vg_kraken-LogVol03 /tablespace_spinning ext4 defaults,noatime 1 2 22,24c22,24 < /dev/mapper/iomemory_vg-iomemVol1 /pg_xlog ext4 defaults,noauto,discard 0 0 < /dev/mapper/iomemory_vg-iomemVol2 /tablespace ext4 defaults,noauto,discard 0 0 < /dev/mapper/iomemory_vg-iomemVol3 /pgdata ext4 defaults,noauto,discard 0 0 --- > /dev/mapper/iomemory_vg-iomemVol1 /pg_xlog ext4 defaults,noatime,noauto,discard 0 0 > /dev/mapper/iomemory_vg-iomemVol2 /tablespace ext4 defaults,noatime,noauto,discard 0 0 > /dev/mapper/iomemory_vg-iomemVol3 /pgdata ext4 defaults,noatime,noauto,discard 0 0 <199 kraken.shore.mbari.org //etc> diff rc.local.130712 rc.local 7a8,9 > blockdev --setra 8192 /dev/mapper/vg_kraken-LogVol04 > blockdev --setra 8192 /dev/mapper/iomemory_vg-iomemVol3 pgbench-tools results at: http://kraken.shore.mbari.org/pgbench-tools/results.130715.AfterOSConfigChanges_tests_61_132/index.htm
Posted by mccann at Jul 12, 2013 22:16
|
|
Modified postgresql.conf based on lessons learned on pgtest. Here's the diff: -bash-4.1$ diff postgresql.conf.130716 postgresql.conf 59c59,60 < #listen_addresses = 'localhost' # what IP address(es) to listen on; --- > # Allow connections from mbari hosts inside our firewall - mpm 7 May 2013 > listen_addresses = '*' # what IP address(es) to listen on; 64c65,66 < max_connections = 100 # (change requires restart) --- > # Changed max_connections from 100 to 10 corresponding to increase in work_mem - mpm 22 May 2013 > max_connections = 10 # (change requires restart) 109c111 < shared_buffers = 32MB # min 128kB --- > shared_buffers = 3GB # min 128kB 118c120,122 < #work_mem = 1MB # min 64kB --- > > # Changed from 1 MB to 400MB to support large STOQS sorts - mpm 22 May 2013 > work_mem = 400MB # min 64kB 174c178,179 < #checkpoint_segments = 3 # in logfile segments, min 1, 16MB each --- > # pgbench-tools indicated high checkpoint frequency, adjust with guidance from p.137. dr 3 July 2013 > checkpoint_segments = 10 # in logfile segments, min 1, 16MB each 176c181 < #checkpoint_completion_target = 0.5 # checkpoint target duration, 0.0 - 1.0 --- > checkpoint_completion_target = 0.9 # checkpoint target duration, 0.0 - 1.0 244c249,251 < #random_page_cost = 4.0 # same scale as above --- > > # Changed to 1.01 per Greg Smith's suggestion on postgresql-perform list - seems to improve STOQS mp query performance - mpm 14 May 2013 > random_page_cost = 1.01 # same scale as above 359c366,367 < #log_min_duration_statement = -1 # -1 is disabled, 0 logs all statements --- > # Log all queries taking longer than 1 second. mpm 15 July 2013 > log_min_duration_statement = 1000 # -1 is disabled, 0 logs all statements 377c385,387 < #log_line_prefix = '' # special values: --- > # Set timestamp for log files to be compatible with pgFouine. mpm 15 July 2013 > log_line_prefix = '%t [%p]: [%l-1] user=%u, db=%d' > # special values: 399c409,410 < #log_temp_files = -1 # log temporary files equal or larger --- > # Turned on logging of temp files. See $PGDATA/base/<db oid>/pgsql_tmp for what a DB is doing - mpm 15 May 2013 > log_temp_files = 0 # log temporary files equal or larger 482c493,494 < #timezone = '(defaults to server environment setting)' --- > # Severs running STOQS databases must have timezone set to 'GMT' > timezone = 'GMT' pgbench-tools results at: http://kraken.shore.mbari.org/pgbench-tools/results.130716.AfterPostgresql.confChanges_tests_133_204/index.htm (as expected, no appreciable changes in performance)
Posted by mccann at Jul 16, 2013 12:05
|
|
Installed STOQS on kraken in /u/stoqsadm/dev/stoqshg_kraken. First time installing on a system with limited sudo privileges. The 'make install's for gdal and mapserver required help tickets for real root to do. Several additional packages needed to be installed and some problems were discovered when running loaders/loadTestData.py. Details documented in these change sets: https://code.google.com/p/stoqs/source/detail?r=fa16cdaf4acd4e7d4dd793c9eee9ca9319d21ff0
Posted by mccann at Jul 17, 2013 16:28
|
|
Fixed problems with stoqs install on kraken:
Posted by mccann at Jul 19, 2013 14:45
|
|
Doing a little exercise of STOQS on kraken. So far performance is similar to odss-staging. Turned down log_min_duration to 100ms in order to see some queries logged. Installing pgFouine to analyze postgresql logs: sudo yum install pgfouine Edit timezone setting in /etc/php.ini: > diff /etc/php.ini.130723 /etc/php.ini 946c946 < ;date.timezone = --- > date.timezone = America/Los_Angeles Create a web directory for pgFouine reports and test running it: sudo mkdir /var/www/html/pgfouine sudo chown postgres /var/www/html/pgfouine pgfouine.php -logtype stderr -file /var/lib/pgsql/9.1/data/pg_log/postgresql-Mon.log -format html > /var/www/html/pgfouine/InitialTest.html Results viewable at http://kraken.shore.mbari.org/pgfouine/InitialTest.html
Posted by mccann at Jul 23, 2013 09:42
|
|
Configured Postgresql logging for production operation with datestamped log files and decreased log_min_duration to 10ms to capture more STOQS query activity: $ diff postgresql.conf.130723 postgresql.conf 297c297 < log_filename = 'postgresql-%a.log' # log file name pattern, --- > log_filename = 'postgresql-%Y%m%d.log' # log file name pattern, 367c367 < log_min_duration_statement = 100 # -1 is disabled, 0 logs all statements --- > log_min_duration_statement = 10 # -1 is disabled, 0 logs all statements pgFouine now: pgfouine.php -logtype stderr -file /var/lib/pgsql/9.1/data/pg_log/postgresql-20130723.log -format html > /var/www/html/pgfouine/postgresql-20130723.html gives: http://kraken.shore.mbari.org/pgfouine/postgresql-20130723.html The pgfouine.php command is something we can put into cron, or just run it when we need to and examine the reports in http://kraken.shore.mbari.org/pgfouine/.
Posted by mccann at Jul 23, 2013 15:23
|
|
Last Thursday afternoon we exercised kraken's FusionIO drives with multiple parallel loads of STOQS campaign data. About 6 parallel loads were started around 2pm local time. Here is the sar report for that day as seen with this command: sar -f /var/log/sa/sa01 -b -d > kraken_sar_1aug2013.txt The highest %util was 12.59% at 2:20 PM. We're barely scratching the surface with the capabilities of this machine.
Posted by mccann at Aug 05, 2013 16:19
|
